﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace DAL_Builder
{
    public partial class MainFormA : Form
    {
        public List<string> dbNames = new List<string>();

        public SqlConnection conn = null;
        public MainFormA()
        {
            InitializeComponent();
            getAllDbName();
            conn = new SqlConnection("Data Source=.;Initial Catalog=master;Integrated Security=True;MultipleActiveResultSets=True");
            conn.Open();
        }
        /// <summary>
        /// 取所有数据库名称
        /// </summary>
        /// <returns></returns>
        public void getAllDbName()
        {
            List<string> sysDBname = new List<string> { "master", "tempdb", "model", "msdb", "ReportServer", "ReportServerTempDB" };
            SqlConnection connhere = new SqlConnection("Data Source=.;Initial Catalog=master;Integrated Security=True;");
            connhere.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM [master].[sys].[sysdatabases]", connhere);
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                string name = reader.GetString(0);
                if (!sysDBname.Contains(name))
                {
                    dbNames.Add(name);
                    listBoxDB.Items.Add(name);
                }
            }
            reader.Close();
            connhere.Close();
        }


        private void listBoxDB_SelectedIndexChanged(object sender, EventArgs e)
        {
            showListBoxTable();
        }


        private void showListBoxTable()
        {
            listBoxTable.Items.Clear();
            checkedListBoxField.Items.Clear();
            string sql = $"use {listBoxDB.SelectedItem.ToString()} ; SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U'";
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                var reader = cmd.ExecuteReader();
                if (string.IsNullOrEmpty(textBoxTableFilter.Text.Trim()))
                {
                    while (reader.Read())
                    {
                        var name = reader.GetString(0);
                        listBoxTable.Items.Add(name);
                    }
                }
                else
                {
                    while (reader.Read())
                    {
                        var name = reader.GetString(0);
                        if (name.ToLower().Contains(textBoxTableFilter.Text.Trim().ToLower()))
                        {
                            listBoxTable.Items.Add(name);
                        }
                    }
                }
                reader.Close();
            }
        }

        private void textBoxDBFilter_TextChanged(object sender, EventArgs e)
        {
            listBoxDB.Items.Clear();
            if (string.IsNullOrEmpty(textBoxDBFilter.Text.Trim()))
            {
                foreach (var one in dbNames)
                {
                    listBoxDB.Items.Add(one);
                }
            }
            else
            {
                var rdb = dbNames.Where(v => v.ToLower().Contains(textBoxDBFilter.Text.Trim().ToLower()));
                foreach (var one in rdb)
                {
                    listBoxDB.Items.Add(one);
                }
            }

            listBoxTable.Items.Clear();

        }

        private void textBoxTableFilter_TextChanged(object sender, EventArgs e)
        {
            showListBoxTable();
        }

        private void listBoxTable_SelectedIndexChanged(object sender, EventArgs e)
        {
            Console.WriteLine(listBoxTable.SelectedItem.ToString());
            showField();


        }

        private void showField()
        {
            checkedListBoxField.Items.Clear();
            var table = GetTableInfo();
            if (table.Fields.Count > 0)
            {
                foreach (var one in table.Fields)
                {
                    checkedListBoxField.Items.Add(one.Name);
                }
            }
        }

        private TableInfo GetTableInfo()
        {
            string hereSql = $"select * from {listBoxDB.SelectedItem.ToString()}.dbo.{listBoxTable.SelectedItem.ToString()} ";
            TableInfo table = new TableInfo();
            table.FullName=$"{listBoxDB.SelectedItem.ToString()}.dbo.{listBoxTable.SelectedItem.ToString()}";
            table.Name = listBoxTable.SelectedItem.ToString();
            table.Fields = new List<Field>();
            table.PrimaryKeyName = null;
            using (SqlCommand cmd = new SqlCommand(hereSql, conn))
            {
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                var cls = ds.Tables[0].Columns;
                for (int clsIndex = 0; clsIndex < cls.Count; clsIndex++)
                {
                    table.Fields.Add(new Field() { Name = cls[clsIndex].ColumnName, FieldType = cls[clsIndex].DataType, Type = null });
                }
                //主键问题
                if (ds.Tables[0].PrimaryKey.Length > 0)
                {
                    table.PrimaryKeyName = ds.Tables[0].PrimaryKey[0].ColumnName;
                }
                
            }
            return table;
        }

        private void btnBuildEntity_Click(object sender, EventArgs e)
        {
            if (listBoxDB.SelectedItem == null || listBoxTable.SelectedItem == null)
            {
                MessageBox.Show("未选择数据表");
            }
            else
            {
                richTextBoxCopy.Clear();
                var table = GetTableInfo();
                List<string> listEntity = Ado_Builder.Build_Entity(table);
                foreach (var str in listEntity)
                {
                    richTextBoxCopy.AppendText(str + "\n");
                }
            }

        }

        private void btnInsert_Click(object sender, EventArgs e)
        {
            if (listBoxDB.SelectedItem == null || listBoxTable.SelectedItem == null)
            {
                MessageBox.Show("未选择数据表");
            }
            else
            {
                richTextBoxCopy.Clear();
                var table = GetTableInfo();
                List<string> list = Ado_Builder.Build_Add(table);

                foreach (var str in list)
                {
                    richTextBoxCopy.AppendText(str + "\n");
                }
            }

        }

        private void btnDelete_Click(object sender, EventArgs e)
        {

            if (listBoxDB.SelectedItem == null || listBoxTable.SelectedItem == null)
            {
                MessageBox.Show("未选择数据表");
                return;
            }
            if (checkedListBoxField.CheckedItems.Count <= 0)
            {
                MessageBox.Show("未选择条件字段");
                return;
            }
            else
            {
                richTextBoxCopy.Clear();
                var table = GetTableInfo();
                string whereLink = "";
                if (radioBtnAnd.Checked)
                {
                    whereLink = "and";
                }
                if (radioBtnOr.Checked)
                {
                    whereLink = "or";
                }
                Console.WriteLine(whereLink);
                List<string> whereFields = new List<string>();
                foreach (var one in checkedListBoxField.CheckedItems)
                {
                    whereFields.Add(one.ToString());
                }

                List<string> list = Ado_Builder.Build_Delete(table, whereFields, whereLink);

                foreach (var str in list)
                {
                    richTextBoxCopy.AppendText(str + "\n");
                }
            }

           


            
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {

            if (listBoxDB.SelectedItem == null || listBoxTable.SelectedItem == null)
            {
                MessageBox.Show("未选择数据表");
                return;
            }
            if (checkedListBoxField.CheckedItems.Count <= 0)
            {
                MessageBox.Show("未选择条件字段");
                return;
            }
            else
            {
                richTextBoxCopy.Clear();
                var table = GetTableInfo();
                string whereLink = "";
                if (radioBtnAnd.Checked)
                {
                    whereLink = "and";
                }
                if (radioBtnOr.Checked)
                {
                    whereLink = "or";
                }
                Console.WriteLine(whereLink);
                List<string> whereFields = new List<string>();
                foreach (var one in checkedListBoxField.CheckedItems)
                {
                    whereFields.Add(one.ToString());
                }
                List<string> list = Ado_Builder.Build_Update(table, whereFields, whereLink);
                foreach (var str in list)
                {
                    richTextBoxCopy.AppendText(str + "\n");
                }
            }
        }

        private void btnSelect_Click(object sender, EventArgs e)
        {
            if (listBoxDB.SelectedItem == null || listBoxTable.SelectedItem == null)
            {
                MessageBox.Show("未选择数据表");
                return;
            }
            if (checkedListBoxField.CheckedItems.Count <= 0)
            {
                MessageBox.Show("未选择条件字段");
                return;
            }
            else
            {
                richTextBoxCopy.Clear();
                var table = GetTableInfo();
                string whereLink = "";
                if (radioBtnAnd.Checked)
                {
                    whereLink = "and";
                }
                if (radioBtnOr.Checked)
                {
                    whereLink = "or";
                }
                Console.WriteLine(whereLink);
                List<string> whereFields = new List<string>();
                foreach (var one in checkedListBoxField.CheckedItems)
                {
                    whereFields.Add(one.ToString());
                }


                List<string> list = Ado_Builder.Build_SelectOne(table, whereFields, whereLink);
                foreach (var str in list)
                {
                    richTextBoxCopy.AppendText(str + "\n");
                }


            }
        }

        private void btnSelectPage_Click(object sender, EventArgs e)
        {
            if (listBoxDB.SelectedItem == null || listBoxTable.SelectedItem == null)
            {
                MessageBox.Show("未选择数据表");
                return;
            }

            richTextBoxCopy.Clear();
            var table = GetTableInfo();
            string whereLink = "";
            if (radioBtnAnd.Checked)
            {
                whereLink = "and";
            }
            if (radioBtnOr.Checked)
            {
                whereLink = "or";
            }
            Console.WriteLine(whereLink);
            List<string> whereFields = new List<string>();
            foreach (var one in checkedListBoxField.CheckedItems)
            {
                whereFields.Add(one.ToString());
            }


            List<string> list = Ado_Builder.Build_SelectInPage(table, whereFields, whereLink);
            foreach (var str in list)
            {
                richTextBoxCopy.AppendText(str + "\n");
            }
            richTextBoxCopy.AppendText("\n");
            list = Ado_Builder.Build_SelectCount(table, whereFields, whereLink);
            foreach (var str in list)
            {
                richTextBoxCopy.AppendText(str + "\n");
            }

        }
    }
}
